﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using Pixysoft.Framework.XSchEmA.Entity;
using System.Data;
using System.Data.OracleClient;


namespace Pixysoft.Framework.XSchEmA.Database
{
    class OracleClientSchemaDownloader
    {
        public void GetTables(OracleConnection conn, DatabaseSchema schema, string user)
        {
            lock (typeof(OracleClientSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                DataTable table = conn.GetSchema("Tables", new string[] { user });
                foreach (DataRow row in table.Rows)
                {
                    TableSchema tableSchema = new TableSchema(row["TABLE_NAME"].ToString().ToUpper().Trim());
                    schema.Tables.Add(tableSchema);
                }
            }
        }

        public void GetColumns(OracleConnection conn, DatabaseSchema schema, string user)
        {
            lock (typeof(OracleClientSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                //初始化链接
                OracleCommand command = new OracleCommand();
                command.Connection = conn;
                command.Parameters.Clear();
                command.CommandText = "SELECT TABLE_NAME,DATA_DEFAULT,COLUMN_NAME,NULLABLE,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE FROM USER_TAB_COLUMNS WHERE TABLE_NAME=:TABLENAME";
                OracleParameter para = new OracleParameter(":TABLENAME", OracleType.VarChar);
                command.Parameters.Add(para);

                OracleDataAdapter adapter = new OracleDataAdapter(command);

                //填充字段注释
                foreach (TableSchema tableSchema in schema.Tables)
                {
                    para.Value = tableSchema.TableName;
                    DataTable table = new DataTable();
                    adapter.Fill(table);

                    foreach (DataRow row in table.Rows)
                    {
                        string tablename = row["TABLE_NAME"].ToString().ToUpper().Trim();
                        string defaultValue = row["DATA_DEFAULT"].ToString().ToUpper().Trim();

                        if (tablename != tableSchema.TableName)
                        {
                            LoggerHelper.Info(tablename);
                            continue;
                        }

                        ColumnSchema columnSchema = new ColumnSchema();
                        columnSchema.TableName = tableSchema.TableName;
                        columnSchema.ColumnName = row["COLUMN_NAME"].ToString().ToUpper().Trim();
                        columnSchema.IsNullable = row["NULLABLE"].ToString().Trim() == "Y" ? true : false;
                        columnSchema.DefaultValue = defaultValue == "" ? null : defaultValue;

                        columnSchema.DataType = new DataTypeSchema(row["DATA_TYPE"].ToString().Trim(),
                            row["DATA_LENGTH"].ToString().Trim() != "" ? Int64.Parse(row["DATA_LENGTH"].ToString().Trim()) : -1,
                            row["DATA_PRECISION"].ToString().Trim() != "" ? int.Parse(row["DATA_PRECISION"].ToString().Trim()) : -1,
                            row["DATA_SCALE"].ToString().Trim() != "" ? int.Parse(row["DATA_SCALE"].ToString().Trim()) : -1);

                        if (columnSchema.DataType.DataType == "NUMBER" && columnSchema.DataType.Precision == -1)
                            columnSchema.DataType = new DataTypeSchema(OracleDataTypeCollection.INTEGER.ToString(), -1, -1, -1);

                        switch (columnSchema.DataType.DataType)//调整
                        {
                            case "NUMBER":
                            case "LONG":
                            case "DATE":
                            case "ROWID":
                            case "CLOB":
                            case "NCLOB":
                            case "BLOB":
                            case "BFILE":
                            case "FLOAT":
                            case "BINARY_DOUBLE":
                            case "BINARY_FLOAT":
                            case "URITYPE":
                                columnSchema.DataType = new DataTypeSchema(columnSchema.DataType.DataType, -1,
                                    columnSchema.DataType.Precision, columnSchema.DataType.Scale);
                                break;

                            case "NCHAR":
                            case "NVARCHAR2":
                                columnSchema.DataType = new DataTypeSchema(columnSchema.DataType.DataType, columnSchema.DataType.Length / (int)2, -1, -1);
                                break;
                            default:
                                break;
                        }

                        tableSchema.Columns.Add(columnSchema);

                    }
                }
            }
        }

        public void GetComment(OracleConnection conn, DatabaseSchema schema, string user)
        {
            lock (typeof(OracleClientSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                //初始化链接
                OracleCommand command = new OracleCommand();
                command.Connection = conn;
                command.CommandText = "SELECT TABLE_NAME,COMMENTS FROM USER_TAB_COMMENTS";
                OracleDataAdapter adapter = new OracleDataAdapter(command);

                //获得表注释
                DataTable tablecomment = new DataTable();
                adapter.Fill(tablecomment);

                //填充表注释
                foreach (DataRow row in tablecomment.Rows)
                {
                    string tablename = row["TABLE_NAME"].ToString();

                    TableSchema table = schema.GetTable(tablename);

                    if (table == null)
                    {
                        LoggerHelper.Info(tablename);
                        continue;
                    }

                    table.Comment = row["COMMENTS"].ToString();
                }

                //设置字段注释链接
                //command.Dispose();
                //command = new OracleCommand();
                command.Parameters.Clear();
                command.CommandText = "SELECT TABLE_NAME, COLUMN_NAME, COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME=:TABLENAME";
                OracleParameter para = new OracleParameter(":TABLENAME", OracleType.VarChar);
                command.Parameters.Add(para);
                //adapter.Dispose();
                //adapter = new OracleDataAdapter(command);


                //填充字段注释
                foreach (TableSchema tableSchema in schema.Tables)
                {
                    para.Value = tableSchema.TableName;
                    DataTable table = new DataTable();
                    adapter.Fill(table);
                    foreach (DataRow row in table.Rows)
                    {
                        string tablename = row["TABLE_NAME"].ToString();
                        string columnname = row["COLUMN_NAME"].ToString();
                        string comment = row["COMMENTS"].ToString();

                        if (tablename.ToUpper().Trim() != tableSchema.TableName)
                        {
                            LoggerHelper.Info(tablename);
                            continue;
                        }

                        ColumnSchema column = tableSchema.GetColumn(columnname);
                        if (column == null)
                        {
                            LoggerHelper.Info(columnname);
                            continue;
                        }

                        column.Comment = comment;
                    }
                }
            }
        }

        public void GetConstraints(OracleConnection conn, DatabaseSchema schema, string user)
        {
            lock (typeof(OracleClientSchemaDownloader))
            {
                if (((conn.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
                {
                    conn.Open();
                }

                List<string> pkList = GetPrimaryKeys(conn, schema, user);

                List<string> uniqueList = GetUniques(conn, schema, user);

                Dictionary<string, string> fkList = GetForeignKeys(conn, schema, user);//KEY=FKNAME, VALUE=CONSNAME

                //初始化链接
                OracleCommand command = new OracleCommand();
                command.Connection = conn;
                command.Parameters.Clear();
                command.CommandText = "SELECT TABLE_NAME,CONSTRAINT_NAME,COLUMN_NAME,POSITION FROM ALL_CONS_COLUMNS WHERE OWNER='" + user + "' AND POSITION IS NOT NULL";
                OracleDataAdapter adapter = new OracleDataAdapter(command);

                DataTable columnTable = new DataTable();
                adapter.Fill(columnTable);

                foreach (DataRow row in columnTable.Rows)
                {
                    string constName = row["CONSTRAINT_NAME"].ToString().ToUpper().Trim();
                    string tableName = row["TABLE_NAME"].ToString().ToUpper().Trim();
                    string columnName = row["COLUMN_NAME"].ToString().ToUpper().Trim();
                    int index = -1;
                    try { index = int.Parse(row["POSITION"].ToString()) - 1; }
                    catch
                    {
                        continue;
                    }

                    if (!schema.ContainedTable(tableName))
                    {
                        LoggerHelper.Info(tableName);
                        continue;
                    }

                    if (pkList.Contains(constName))
                        AddPrimaryKey(schema, constName, tableName, columnName, index);

                    if (uniqueList.Contains(constName))
                        AddUnique(schema, constName, tableName, columnName, index);

                    if (fkList.ContainsKey(constName))
                        AddForeignKey(schema, constName, fkList[constName], tableName, columnName, index);
                }
            }
        }

        private void AddForeignKey(DatabaseSchema schema, string consName, string pKeyConsName, string tableName, string columnName, int index)
        {
            ForeignKeySchema fkSchema = schema.GetForeignKeyByName(consName);

            if (fkSchema == null)
            {
                fkSchema = new ForeignKeySchema();
                fkSchema.ConsName = consName;
                fkSchema.PkeyConsName = pKeyConsName;
                fkSchema.FkTableName = tableName;
                schema.ForeignKeys.Add(fkSchema);
            }

            SchemaUtility.AddToList<string>(fkSchema.FkColumnNames, columnName, index);
        }

        private void AddUnique(DatabaseSchema schema, string constName, string tableName, string columnName, int index)
        {
            UniqueSchema uSchema = schema.GetUniqueByName(constName);

            if (uSchema == null)
            {
                uSchema = new UniqueSchema();
                uSchema.ConsName = constName;
                uSchema.TableName = tableName;
                schema.Uniques.Add(uSchema);
            }

            SchemaUtility.AddToList<string>(uSchema.ColumnNames, columnName, index);
        }

        private void AddPrimaryKey(DatabaseSchema schema, string constName, string tableName, string columnName, int index)
        {
            PrimaryKeySchema pkSchema = schema.GetPrimaryKeyByName(constName);

            if (pkSchema == null)
            {
                pkSchema = new PrimaryKeySchema();
                pkSchema.ConsName = constName;
                pkSchema.TableName = tableName;
                schema.PrimaryKeys.Add(pkSchema);
            }

            SchemaUtility.AddToList<string>(pkSchema.ColumnNames, columnName, index);
        }

        private List<string> GetPrimaryKeys(OracleConnection conn, DatabaseSchema schema, string user)
        {
            DataTable constTable = conn.GetSchema("PrimaryKeys", new string[] { user });

            List<string> pkList = new List<string>();

            foreach (DataRow row in constTable.Rows)
                if (!pkList.Contains(row["CONSTRAINT_NAME"].ToString().ToUpper().Trim()))
                    pkList.Add(row["CONSTRAINT_NAME"].ToString());

            return pkList;
        }

        private List<string> GetUniques(OracleConnection conn, DatabaseSchema schema, string user)
        {
            DataTable constTable = conn.GetSchema("UniqueKeys", new string[] { user });

            List<string> uniqueList = new List<string>();

            foreach (DataRow row in constTable.Rows)
                if (!uniqueList.Contains(row["CONSTRAINT_NAME"].ToString().ToUpper().Trim()))
                    uniqueList.Add(row["CONSTRAINT_NAME"].ToString());

            return uniqueList;
        }

        private Dictionary<string, string> GetForeignKeys(OracleConnection conn, DatabaseSchema schema, string user)
        {
            DataTable constTable = conn.GetSchema("ForeignKeys", new string[] { user });

            Dictionary<string, string> fkList = new Dictionary<string, string>();

            foreach (DataRow row in constTable.Rows)
                fkList.Add(row["FOREIGN_KEY_CONSTRAINT_NAME"].ToString().ToUpper().Trim(), row["PRIMARY_KEY_CONSTRAINT_NAME"].ToString());

            return fkList;
        }

    }
}
